CREATE FUNCTION [dbo].[asi_ConsecutiveYearsGiving](@Id varchar(10))
--
-- Returns the number of years of consecutive giving for the contact identified by @Id
--
-- First gift calendar year counts as 1. Add 1 for each consecutive cal year they make a gift.
-- If the donor hasn’t made any gifts by the end of the cal year it
-- drops back to 0 on Jan 1st the next year.
--
-- This is used by Giving Summary display
--
RETURNS int AS
BEGIN

DECLARE @GiftHistory TABLE
(
  [GiftYear] int,
  [Amount] money  
)

DECLARE @StartYear int
DECLARE @EndYear int
DECLARE @C int
DECLARE @Result int
DECLARE @ThisYearAmt money

SET @StartYear = 0
SET @EndYear = 0
SET @Result = 0
SET @ThisYearAmt = 0
-- Determine range of years this contact has gifts for
SELECT @StartYear = ISNULL( MIN( DATEPART( YYYY, [TransactionDate] ) ), 99),
@EndYear = ISNULL( MAX( DATEPART( YYYY, [TransactionDate] ) ), -1)
  FROM [dbo].[vGift]
WHERE [ID] = @Id

-- Populate table with rows for range of years
SET @C = @StartYear
WHILE @C <= @EndYear
BEGIN
    INSERT @GiftHistory( [GiftYear], [Amount]) VALUES ( @C, 0 )
    SET @C = @C + 1
END   

-- Update amount given per year   
UPDATE @GiftHistory SET Amount =
          (SELECT ISNULL( SUM( [Amount] ), 0)
             FROM [dbo].[vGift]
           WHERE [ID] = @Id
                 AND DATEPART( YYYY, [TransactionDate] ) = GiftYear)

-- Determine number of consecutive years
SET @C = @StartYear
WHILE @C <= @EndYear
BEGIN
    SELECT @ThisYearAmt = Amount
      FROM @GiftHistory
    WHERE [GiftYear] = @C
    IF @ThisYearAmt > 0
    BEGIN
        SET @Result = @Result + 1
    END
    IF @ThisYearAmt <= 0
    BEGIN
        SET @Result = 0
    END
    SET @C = @C + 1
END   

RETURN @Result
END


GO